<?php
include('db_connection.php');

class manageUser {

    function export_csv($aff_id, $postedData = '') {
        
        $condition_search = '';
        $value = '';
        if (isset($postedData['search_value'])) {
            $value = trim($postedData['search_value']);
            if (!empty($value)) {
                $cond_where = 'AND ';
                $condition_search = $cond_where . " (username like '%" . $value . "%' OR email like '%" . $value . "%')";
            }
        }

        if (isset($postedData['from_date']) && $postedData['from_date'] == '1970-01-01') {
            $from_date = date('Y-m-d', strtotime(trim($postedData['from_date'])));
            $to_date = date('Y-m-d', strtotime(trim($postedData['to_date'])));
            $cond_where = 'AND ';
            $condition_search .= $cond_where . " ur.create_ts BETWEEN '" . $from_date . "' AND '" . $to_date . "' ";
        }

        $query = "SELECT  SUM(c.`commission`) as total_user_commission,u.id as user_id, u.*  FROM tbl_user AS u 
JOIN tbl_commission AS c ON c.`trainee_id` = u.`id` 
JOIN tbl_user_details AS ud ON c.`trainee_detail_id` = ud.`id` 
WHERE  u.reffered_by = 'a_" . $aff_id . "' AND ud.`is_refund` = '0' " . $condition_search . "
GROUP BY u.username ORDER BY u.username DESC ";
        

      
        
        $data = mysql_query($query) or die(mysql_error());
        //$output = fopen('php://output', 'w');

		$filename = 'csv/Affiliate_user.csv';
		$output = fopen($filename, 'w');
		
        $total_amount = '0';
        $pending_amount = '0';
        $paid_amount = 0;

        $affiliate_query = mysql_query("select * from tbl_affiliate where id = '" . $aff_id . "'");
        $affiliate_data = mysql_fetch_assoc($affiliate_query);




        fputcsv($output, array('Affiliate Detail'));
        fputcsv($output, array('Name:', $affiliate_data['username']));
        fputcsv($output, array('Email:', $affiliate_data['email_id']));
        fputcsv($output, array('Withdraw Min Amount:', $affiliate_data['withdraw_min_amount']));
        fputcsv($output, array('Withdraw Max Amount:', $affiliate_data['withdraw_max_amount']));
        fputcsv($output, array(''));
        fputcsv($output, array(''));

        fputcsv($output, array('UserName', 'Email', 'Date', 'Price', 'Active', 'Paid'));
        $total_amount = '0';
        $pending_amount = '0';
        $paid_amount = '0';
        while ($rows = mysql_fetch_array($data)) {
        $select_paidamount = "SELECT SUM(total_amount) AS total_amount FROM tbl_aff_transfer  WHERE at_affilate_id= '" . $aff_id . "'";
        $query_paidamount = mysql_query($select_paidamount) or die(mysql_error());
        $total_paidamount = mysql_fetch_assoc($query_paidamount);
        if ($rows['is_active'] && $rows['is_paid']) {
            $total_amount += $rows['total_user_commission'];
        }
        $paid_amount = $total_paidamount['total_amount'];
        $pending_amount = $total_amount - $total_paidamount['total_amount'];
                                                                                                           
                                                
            
            $row['username'] = $rows['username'];
            $row['email'] = $rows['email'];
            $row['date'] = $rows['create_ts'];
            if ($rows['is_active'] && $rows['is_paid']) {
                $row['Price'] = $rows['total_user_commission'];;
            } else {
                $row['Price'] = '-';
            }
            if ($rows['is_active'] == 1) {
                $row['active'] = 'Yes';
            } else {
                $row['active'] = 'No';
            }
            if ($rows['is_paid'] == 1) {
                $row['is_paid'] = 'Yes';
            } else {
                $row['is_paid'] = 'No';
            }

            fputcsv($output, $row);
        }
        
        fputcsv($output, array(''));
        fputcsv($output, array(''));
        fputcsv($output, array('Total Amount:', "$".$total_amount));
        fputcsv($output, array('Paid Amount:', "$".$paid_amount));
        fputcsv($output, array('Pending Amount:', "$".$pending_amount));
        
        
        fputcsv($output, array(''));
        fputcsv($output, array(''));
        fputcsv($output, array('Bank Detail'));
        fputcsv($output, array('Bank Name', 'Account No', 'Transaction Id', 'Transfer Amount', 'Tds(%)', 'Receive Amount','Date','Note'));
        $select_bank = "SELECT * FROM tbl_aff_transfer WHERE at_affilate_id= '" . $aff_id . "' ORDER BY at_id DESC ";
        $query_bank = mysql_query($select_bank) or die(mysql_error());
        while ($rows1 = mysql_fetch_array($query_bank)) {
           $row1['bank_name'] = $rows1['bank_name'];
           $row1['account_number'] = $rows1['account_number'];
           $row1['transaction_id'] = $rows1['transaction_id'];
           $row1['total_amount'] = "$".$rows1['total_amount'];
           $row1['tds'] = $rows1['tds'];
           $row1['total_amount_with_tds'] = "$".$rows1['total_amount_with_tds'];
           $row1['at_date'] = $rows1['at_date'];
           $row1['at_note'] = $rows1['at_note'];
           fputcsv($output, $row1);
        }
        //$filename = "User_" . rand();
        //header('Content-Type: text/csv; charset=utf-8');
        //header('Content-Disposition: attachment; filename=' . $filename . '.csv');
        $filename1 = 'Affiliate_user.csv';
		$this->dl_file('csv/Affiliate_user.csv');
        exit;
    }
  public static function dl_file($file) {

        //First, see if the file exists
        if (!is_file($file)) {
            die(
                    "<div style='text-align:center;'><h1> Sorry, file temporary not available!</h1></div>");
        }

        //Gather relevent info about file
        $len = filesize($file);
        $filename = basename($file);
        $file_extension = strtolower(substr(strrchr($filename, "."), 1));

        //This will set the Content-Type to the appropriate setting for the file
        switch ($file_extension) {
            case "pdf":
                $ctype = "application/pdf";
                break;
            case "exe":
                $ctype = "application/octet-stream";
                break;
            case "zip":
                $ctype = "application/zip";
                break;
            case "doc":
                $ctype = "application/msword";
                break;
            case "xls":
                $ctype = "application/vnd.ms-excel";
                break;
            case "ppt":
                $ctype = "application/vnd.ms-powerpoint";
                break;
            case "gif":
                $ctype = "image/gif";
                break;
            case "png":
                $ctype = "image/png";
                break;
            case "jpeg":
            case "jpg":
                $ctype = "image/jpg";
                break;
            case "mp3":
                $ctype = "audio/mpeg";
                break;
            case "wav":
                $ctype = "audio/x-wav";
                break;
            case "mpeg":
            case "mpg":
            case "mpe":
                $ctype = "video/mpeg";
                break;
            case "mov":
                $ctype = "video/quicktime";
                break;
            case "avi":
                $ctype = "video/x-msvideo";
                break;

            //The following are for extensions that shouldn't be downloaded (sensitive stuff, like php files)
            case "php":
            case "htm":
            case "html":
            case "txt":
                die("<b>Cannot be used for " . $file_extension . " files!</b>");
                break;

            default:
                $ctype = "application/force-download";
        }

        //Begin writing headers
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Cache-Control: public");
        header("Content-Description: File Transfer");

        //Use the switch-generated Content-Type
        header("Content-Type: $ctype");

        //Force the download
        //$header="Content-Disposition: attachment; filename=".$filename.";"; //for force fully download
        $header = "Content-Disposition: inline; filename=" . $filename . ";";
        header($header);
        header("Content-Transfer-Encoding: binary");
        header("Content-Length: " . $len);
        @readfile($file);
        exit;
    }

	
    function del_user($user_id) {

        $del_update = "UPDATE tbl_user SET is_active = '0', update_ts = now() WHERE id='" . $user_id . "'";

        $query_update = mysql_query($del_update) or die(mysql_error());

        $success_msg = "User Has been Sucessfully Inactive";

        return $success_msg;
    }

    function activate_user($user_id) {

        $act_update = "UPDATE tbl_user SET is_active = '1', update_ts = now() WHERE id='" . $user_id . "'";

        $query_update = mysql_query($act_update) or die(mysql_error());

        $success_msg = "User Has been Sucessfully Active";

        return $success_msg;
    }

    function update_refund_user($user_id) {

        $refund_update = "UPDATE tbl_user_details SET is_refund = '1', update_ts = now() WHERE id='" . $user_id . "'";

        $query_update = mysql_query($refund_update) or die(mysql_error());

        $success_msg = "User Has been Sucessfully Active";

        return $success_msg;
    }

    function user_paid_type() {

        if (isset($_POST['pay_type_status'])) {

            $select_usr_dtl = "SELECT * FROM tbl_user_details WHERE id='" . $_POST['userdtlpayerId'] . "'";

            $query_usr_dtl = mysql_query($select_usr_dtl) or die(mysql_error());



            if (mysql_num_rows($query_usr_dtl) > 0) {

                while ($row_usr_dtl = mysql_fetch_array($query_usr_dtl)) {

                    $course_id = $row_usr_dtl['course_id'];

                    $course_fee = '';



                    $sel_course_fee = "SELECT * FROM tbl_courses where id = '" . $course_id . "'";

                    $query_course_fee = mysql_query($sel_course_fee) or die(mysql_error());

                    if (mysql_num_rows($query_usr_dtl) > 0) {

                        while ($row_course_fee = mysql_fetch_array($query_course_fee)) {

                            $course_fee = $row_course_fee['course_fee'];
                        }
                    }



                    $user_id = $row_usr_dtl['user_id'];

                    $insert_cart = "INSERT INTO tbl_cart_payment (user_id,course_id,user_detail_id,payment) values('" . $row_usr_dtl['user_id'] . "','" . $course_id . "','" . $_POST['userdtlpayerId'] . "','" . $course_fee . "')";

                    $query_insert_cart = mysql_query($insert_cart) or die(mysql_error());



                    $sel_bank_status = "SELECT * FROM tbl_bank_status WHERE id='" . $_POST['pay_type_status'] . "'";

                    $course_bank_status = mysql_query($sel_bank_status) or die(mysql_error());

                    if (mysql_num_rows($course_bank_status) > 0) {

                        while ($row_bank_status = mysql_fetch_array($course_bank_status)) {

                            $update_bak_status = "UPDATE tbl_user_details SET via_paid='" . $row_bank_status['type'] . "' WHERE id='" . $_POST['userdtlpayerId'] . "'";

                            $query_bank_status = mysql_query($update_bak_status) or die(mysql_error());
                        }
                    }



                    $update = "UPDATE tbl_user SET is_paid='1', is_active='1', update_ts=now() WHERE id='" . $row_usr_dtl['user_id'] . "'";

                    $user_update = mysql_query($update) or die(mysql_error());

                    $msg = "Bank Detail Updated Successfully";

                    return $msg;
                }
            }
        }
    }

    //tbl_aff_transfer data

    function bankdetail_add($affiliate_id, $page_name = '') {


        $data = $this->get_details($affiliate_id);

        $at_affilate_id = $affiliate_id;
        $bank_name = addslashes($_POST['bank_name']);
        $account_number = addslashes($_POST['account_number']);
        $transaction_id = $_POST['transaction_id'];
        $pending_amount = addslashes($_POST['pending_amount']);
        $total_amount = addslashes($_POST['total_amount']);
        $total_amount_with_tds = $_POST['total_amount_with_tds'];
        $tds = addslashes($_POST['tds']);
        $at_date = date('Y-m-d H:i:s', strtotime($_POST['at_date']));
        $at_note = addslashes($_POST['at_note']);
        //$b_paypal_name = addslashes($_POST['b_paypal_name']);
        //if(count($data) == 0){
        $insert_details = "INSERT into tbl_aff_transfer (`at_affilate_id`, `bank_name`, `account_number`, `transaction_id`,`pending_amount`,`total_amount`,`tds`,`at_date`,`at_note`,`total_amount_with_tds`,`at_createddate`, `at_updateddate`)
           VALUES ('" . $at_affilate_id . "','" . $bank_name . "', '" . $account_number . "', '" . $transaction_id . "', '" . $pending_amount . "', '" . $total_amount . "','" . $tds . "','" . $at_date . "', '" . $at_note . "','" . $total_amount_with_tds . "','" . date('Y-m-d H:i:s') . "','" . date('Y-m-d H:i:s') . "')";
        $rows = mysql_query($insert_details) or die(mysql_error());
        $success_msg = "Bank details Has been Sucessfully Added";
        /* } else{
          $update_details = "UPDATE `tbl_aff_transfer` SET `bank_name`='".$bank_name."',`account_number`= '".$account_number."',`transaction_id`= '".$transaction_id."',`pending_amount`= '".$pending_amount."',`total_amount`= '".$total_amount."',`tds`='".$tds."',`at_updateddate` = '".date('Y-m-d H:i:s')."' WHERE at_affilate_id = '".$at_affilate_id."'";
          $rows = mysql_query($update_details) or die(mysql_error());
          $success_msg = "Bank details Has been Sucessfully Updated";
          } */

        if ($page_name == 'user_affiliate') {
            $path = $_SERVER['PHP_SELF'] . "?aff_prg=1&aff_id=" . $at_affilate_id;
        } else {
            $path = $_SERVER['PHP_SELF'] . "?mng_wdr=1&user_id=" . $at_affilate_id;
        }
        ?>
        <script>
            window.location.href = '<?php echo $path ?>';
        </script>
        <?php
        //return $success_msg;
    }

    function get_details($id) {
        $select = "SELECT * FROM tbl_aff_transfer WHERE at_affilate_id='" . $id . "'";
        $query = mysql_query($select) or die(mysql_error());

        if (mysql_num_rows($query)) {
            $results = array();
            while ($row = mysql_fetch_array($query)) {
                foreach ($row as $k => $v) {
                    $result->$k = $v;
                }

                $results[] = $result;
            }
            return $results;
        }
    }

}
?>